using System;
using System.Collections.Generic;
using System.Text;
using SqlTools.CodeGenerator.Data;
using System.IO;

namespace SqlTools.CodeGenerator.CSharp
{
	public class DbObjectGenerator
	{
		protected Table _table;

		public DbObjectGenerator(Table table)
		{
			_table = table;
		}

		public void CreateDbObject()
		{
			CodeBuilder cb = new CodeBuilder(_table.DataNamespace, _table.DbObjectName);
			cb.Bases.Add("DbBase<" + _table.PrimaryKeyClassName + ", " + _table.BusinessObjectName + ">");
			cb.PartialClass = _table.Project.UseGeneratedPartialClassFormat;

			GenerateUsingStatements(cb);

			CreateDefaultConstructor(cb);
			cb.NewLine();

			CreateInitConstructor(cb);
			cb.NewLine();

			foreach ( Method method in _table.Methods )
			{
				GenerateMethod(cb, method);
			}

			string fileText = cb.GetText();

			string fileName = Path.Combine(_table.Project.RootDirectory, _table.DataDirectory);

			Directory.CreateDirectory(fileName);
			
			string file = _table.DbObjectName;

			if ( _table.Project.UseGeneratedPartialClassFormat )
				file += ".generated";

			file += ".cs";

			fileName = Path.Combine(fileName,  file);


			StreamWriter sw = File.CreateText(fileName);
			sw.Write(fileText);
			sw.Close();

			if ( _table.Project.UseGeneratedPartialClassFormat )
			{
				// create edittable partial class

				fileName = Path.Combine(_table.Project.RootDirectory, _table.DataDirectory);

				file = _table.DbObjectName;

				file += ".cs";

				fileName = Path.Combine(fileName, file);

				// check to see if file exists.  If it does, we do nothing

				if ( !File.Exists(fileName) )
				{

					cb = new CodeBuilder(_table.DataNamespace, _table.DbObjectName);
					cb.PartialClass = true;

					GenerateUsingStatements(cb);

					fileText = cb.GetText();

					sw = File.CreateText(fileName);
					sw.Write(fileText);
					sw.Close();
				}

			}

		}

		public void GenerateUsingStatements(CodeBuilder cb)
		{
			cb.Namespaces.Add("System");
			cb.Namespaces.Add("System.Collections.Generic");
			cb.Namespaces.Add("System.Text");
			cb.Namespaces.Add("SqlTools.DataAccessLibrary");
			cb.Namespaces.Add("System.Data.SqlClient");
			cb.Namespaces.Add("System.Data");
		}

		protected void CreateDefaultConstructor(CodeBuilder cb)
		{
			cb.Append("public ").Append(_table.DbObjectName).Append("()").NewLine();
			cb.SingleLineIndent().Append(": base(\"").Append(_table.Project.DefaultConnectionStringName).Append("\")").NewLine();
			cb.BeginBlock();
			cb.EndBlock();
		}

		protected void CreateInitConstructor(CodeBuilder cb)
		{
			cb.Append("public ").Append(_table.DbObjectName).Append("(string connectionStringName)").NewLine();
			cb.SingleLineIndent().Append(": base(connectionStringName)").NewLine();
			cb.BeginBlock();
			cb.EndBlock();
		}

		protected void GenerateMethod(CodeBuilder cb, Method method)
		{
			switch ( method.Type )
			{
				case MethodType.SelectSingle:
					GenerateSingleSelect(cb, method);
					break;
				case MethodType.SelectMultiple:
					GenerateMultipleSelect(cb, method);
					break;
				case MethodType.Insert:
					GenerateInsert(cb, method);
					break;
				case MethodType.Update:
					GenerateUpdate(cb, method);
					break;
				case MethodType.Delete:
					GenerateDelete(cb, method);
					break;
			}
		}

		private void GenerateSingleSelect(CodeBuilder cb, Method method)
		{
            //TODOS GPC
            if (method == null) return;
            if (method.Parameters == null) return;
            if (method.Parameters.Count == 0) return;
            if (method.Parameters[0].Property == null) return;
			bool isSelectByPK = false;

			if ( method.Parameters.Count == 1 && method.Parameters[0].Property.Type == TablePropertyType.PrimaryKey )
			{
				// generate select by pk
				Dictionary<string, string> parms = new Dictionary<string, string>();
				parms.Add(_table.PrimaryKeyClassName, "key");

				cb.BeginMethod(AccessibilityLevel.Public, _table.BusinessObjectName, method.MethodName, parms, OverloadType.Override);

				isSelectByPK = true;
			}
			else
			{
				Dictionary<string, string> parms = new Dictionary<string, string>();
				foreach ( MethodParameter param in method.Parameters )
				{
					parms.Add(param.Property.CSharpDataType, param.ParameterName);
				}

				cb.BeginMethod(AccessibilityLevel.Public, _table.BusinessObjectName, method.MethodName, parms);
			}

			string objName = NamingHelper.ClassVariableName(_table.BusinessObjectName);

			cb.Append(_table.BusinessObjectName).Append(" ").Append(objName).Append(" = null").NewLine(true);
			cb.Append("using (SqlConnection conn = new SqlConnection(_connectionString) )").NewLine();
			cb.BeginBlock();
			cb.Append("SqlCommand cmd = new SqlCommand(\"").Append(method.StoredProcedureName).Append("\", conn)").NewLine(true);
			cb.Append("cmd.CommandType = CommandType.StoredProcedure").NewLine(true);
			cb.NewLine();
			if ( method.Parameters.Count == 1 && method.Parameters[0].Property.Type == TablePropertyType.PrimaryKey )
			{
				// select by pk
				PrimaryKeyTableProperty pkprop = _table.GetPrimaryKey();
				foreach ( TableProperty prop in pkprop.Properties )
				{
					GenerateParameter(cb, prop);
					cb.Append(".Value = key.").Append(prop.PropertyName).NewLine(true);
				}
			}
			else
			{
				foreach ( MethodParameter param in method.Parameters )
				{
					GenerateParameter(cb, param.Property);
					cb.Append(".Value = ").Append(param.ParameterName).NewLine(true);
				}
			}
			cb.NewLine();
			cb.Append("conn.Open()").NewLine(true);
			cb.NewLine();
			cb.Append("SqlDataReader sdr = cmd.ExecuteReader()").NewLine(true);
			cb.NewLine();
			cb.Append("if ( sdr.Read() )").NewLine();
			cb.BeginBlock();
			cb.Append(objName).Append(" = ").Append(_table.BusinessObjectName).Append(".CreateFromReader(sdr)").NewLine(true);
			cb.Append(objName).Append("._ConnectionStringName = _connectionStringName").NewLine(true);
			cb.EndBlock();
			cb.Append("sdr.Close()").NewLine(true);
			cb.NewLine();
			cb.Append("conn.Close()").NewLine(true);
			if ( _table.BaseTable != null )
			{
				string dbObjectName = NamingHelper.ClassVariableName(_table.BaseTable.DbObjectName);
				cb.NewLine();
				cb.Append("// load base class").NewLine();
				cb.Append(_table.BaseTable.DbObjectName).Append(" ").Append(dbObjectName).Append(" = new ").Append(_table.BaseTable.DbObjectName).Append("(_connectionStringName)").NewLine(true);
				cb.NewLine();
				cb.Append(dbObjectName).Append(".SelectByPK(").Append(objName).Append(", ").Append(objName).Append(".Key)").NewLine(true);
			}
			cb.EndBlock();
			cb.Append("return ").Append(objName).NewLine(true);
			cb.EndMethod();

			if ( isSelectByPK && _table.InheritedTables.Count > 0 )
			{
				cb.NewLine();
				GenerateBaseSingleSelect(cb, method);
			}
		}

		private void GenerateBaseSingleSelect(CodeBuilder cb, Method method)
		{
			string objName = NamingHelper.ClassVariableName(_table.BusinessObjectName);

			Dictionary<string, string> parms = new Dictionary<string, string>();
			parms.Add(_table.BusinessObjectName, objName);
			parms.Add(_table.PrimaryKeyClassName, "key");

			cb.BeginMethod(AccessibilityLevel.Public, "bool", "SelectByPK", parms);

			cb.Append("bool objectFound = false").NewLine(true);
			cb.Append("using (SqlConnection conn = new SqlConnection(_connectionString) )").NewLine();

			cb.BeginBlock();
			cb.Append("SqlCommand cmd = new SqlCommand(\"").Append(method.StoredProcedureName).Append("\", conn)").NewLine(true);
			cb.Append("cmd.CommandType = CommandType.StoredProcedure").NewLine(true);
			cb.NewLine();
			// select by pk
			PrimaryKeyTableProperty pkprop = _table.GetPrimaryKey();
			foreach ( TableProperty prop in pkprop.Properties )
			{
				cb.Append("cmd.Parameters.Add(\"@").Append(prop.ColumnName).Append("\",SqlDbType.").Append(prop.DataType.ToString()).Append(").Value = key.").Append(prop.PropertyName).NewLine(true);
			}
			cb.NewLine();
			cb.Append("conn.Open()").NewLine(true);
			cb.NewLine();
			cb.Append("SqlDataReader sdr = cmd.ExecuteReader()").NewLine(true);
			cb.NewLine();
			cb.Append("if ( sdr.Read() )").NewLine();

			cb.BeginBlock();
			cb.Append("objectFound = true").NewLine(true);
			cb.Append(objName).Append(" = ").Append(_table.BusinessObjectName).Append(".CreateFromReader(sdr)").NewLine(true);
			cb.Append(objName).Append("._ConnectionStringName = _connectionStringName").NewLine(true);
			cb.EndBlock();

			cb.Append("sdr.Close()").NewLine(true);
			cb.NewLine();
			cb.Append("conn.Close()").NewLine(true);
			cb.EndBlock();

			cb.Append("return objectFound").NewLine(true);
			cb.EndMethod();


			
		}

		private void GenerateMultipleSelect(CodeBuilder cb, Method method)
		{
			string listName = NamingHelper.PluralClassVariableName(_table.BusinessObjectName);
			string objName = NamingHelper.ClassVariableName(_table.BusinessObjectName);

			Dictionary<string, string> parms = new Dictionary<string, string>();
			foreach ( MethodParameter param in method.Parameters )
			{
				parms.Add(param.Property.CSharpDataType, param.ParameterName);
			}

			cb.BeginMethod(AccessibilityLevel.Public, "DbList<" + _table.BusinessObjectName + ">", method.MethodName, parms);

			cb.Append("DbList<").Append(_table.BusinessObjectName).Append("> ").Append(listName).Append(" = new DbList<").Append(_table.BusinessObjectName).Append(">()").NewLine(true);
			cb.Append("using ( SqlConnection conn = new SqlConnection(_connectionString) )").NewLine();
			
			cb.BeginBlock();
			cb.Append("SqlCommand cmd = new SqlCommand(\"").Append(method.StoredProcedureName).Append("\", conn)").NewLine(true);
			cb.Append("cmd.CommandType = CommandType.StoredProcedure").NewLine(true);
			cb.NewLine();
			// append parameters

			foreach ( MethodParameter param in method.Parameters )
			{
				cb.Append("cmd.Parameters.Add(\"@").Append(param.Property.ColumnName).Append("\", SqlDbType.").Append(param.Property.DataType.ToString()).Append(").Value = ").Append(param.ParameterName).NewLine(true);
			}
			cb.NewLine();
			cb.Append("conn.Open()").NewLine(true);
			cb.NewLine();
			cb.Append("SqlDataReader sdr = cmd.ExecuteReader()").NewLine(true);
			cb.NewLine();
			cb.Append("while ( sdr.Read() )").NewLine();

			cb.BeginBlock();
			cb.Append(_table.BusinessObjectName).Append(" ").Append(objName).Append(" = ").Append(_table.BusinessObjectName).Append(".CreateFromReader(sdr)").NewLine(true);
			cb.Append(objName).Append("._ConnectionStringName = _connectionStringName").NewLine(true);
			cb.NewLine(); ;
			cb.Append(listName).Append(".Add(").Append(objName).Append(")").NewLine(true);
			cb.EndBlock();

			cb.Append("sdr.Close()").NewLine(true);
			cb.NewLine();
			cb.Append("conn.Close()").NewLine(true);
			cb.NewLine();
			if ( _table.BaseTable != null )
			{
				string dbObjectName = NamingHelper.ClassVariableName(_table.BaseTable.DbObjectName);
				cb.Append("// load base class").NewLine();
				cb.Append(_table.BaseTable.DbObjectName).Append(" ").Append(dbObjectName).Append(" = new ").Append(_table.BaseTable.DbObjectName).Append("(_connectionStringName)").NewLine(true);
				cb.NewLine();
				cb.Append("foreach ( ").Append(_table.BusinessObjectName).Append(" ").Append(objName).Append(" in ").Append(listName).Append(" )").NewLine();
				
				cb.BeginBlock();
				cb.Append(dbObjectName).Append(".SelectByPK(").Append(objName).Append(", ").Append(objName).Append(".Key)").NewLine(true);
				cb.EndBlock();
			}
			cb.EndBlock();
			cb.NewLine();
			cb.Append("return ").Append(listName).NewLine(true);
			cb.EndMethod();
		}

		private void GenerateInsert(CodeBuilder cb, Method method)
		{
			string objName = NamingHelper.ClassVariableName(_table.BusinessObjectName);
			Dictionary<string, string> parms = new Dictionary<string, string>();
			parms.Add(_table.BusinessObjectName, objName);
			parms.Add("SqlConnection", "conn");
			parms.Add("SqlTransaction", "trans");

			cb.BeginMethod(AccessibilityLevel.Public, _table.PrimaryKeyClassName, "Insert", parms, OverloadType.Override);

			cb.Append(objName).Append("._ConnectionStringName = _connectionStringName").NewLine(true);
			cb.NewLine();
			cb.Append("bool destroyConn = false").NewLine(true);
			cb.NewLine();
			cb.Append("if ( conn == null )").NewLine();
			
			cb.BeginBlock();
			cb.Append("destroyConn = true").NewLine(true);
			cb.Append("conn = new SqlConnection(_connectionString)").NewLine(true);
			cb.EndBlock();

			cb.NewLine();
			cb.Append("try").NewLine();
			
			cb.BeginBlock();
			cb.Append("SqlCommand cmd = new SqlCommand(\"").Append(method.StoredProcedureName).Append("\", conn)").NewLine(true);
			cb.Append("cmd.CommandType = CommandType.StoredProcedure").NewLine(true);
			cb.Append("cmd.Transaction = trans").NewLine(true);
			cb.NewLine();
			cb.Append("if ( conn.State != ConnectionState.Open )").NewLine();

			cb.BeginBlock();
			cb.Append("conn.Open()").NewLine(true);
			cb.EndBlock();

			cb.NewLine();
			
			if ( _table.BaseTable != null )
			{
				string dbObjectName = NamingHelper.ClassVariableName(_table.BaseTable.DbObjectName);
				string pkname = NamingHelper.ClassVariableName(_table.BaseTable.PrimaryKeyClassName);

				cb.Append("// First, execute base class insert").NewLine();
				cb.Append(_table.BaseTable.DbObjectName).Append(" ").Append(dbObjectName).Append(" = new ").Append(_table.BaseTable.DbObjectName).Append("(_connectionStringName)").NewLine(true);
				cb.Append(_table.BaseTable.PrimaryKeyClassName).Append(" ").Append(pkname).Append(" = ").Append(dbObjectName).Append(".Insert(").Append(objName).Append(", conn, trans)").NewLine(true);

				PrimaryKeyTableProperty pkprop = _table.BaseTable.GetPrimaryKey();
				foreach ( TableProperty prop in pkprop.Properties )
				{
					cb.Append(objName).Append(".Key.").Append(prop.PropertyName).Append(" = ").Append(pkname).Append(".").Append(prop.PropertyName).NewLine(true);
				}
				cb.NewLine();
			}
			// generate primary key properties (if auto generated)
			PrimaryKeyTableProperty pk = _table.GetPrimaryKey();
			foreach ( TableProperty p in pk.Properties )
			{
				GenerateParameter(cb, p);
				if ( p.AutoGeneratedOnInsert )
				{
					cb.Append(".Direction = ParameterDirection.Output").NewLine(true);
				}
				else
				{
					cb.Append(".Value = ").Append(objName).Append(".Key.").Append(p.PropertyName).NewLine(true);
				}
			}

			// generate base table primary keys
			if ( _table.BaseTable != null )
			{
				PrimaryKeyTableProperty pkprop = _table.BaseTable.GetPrimaryKey();
				foreach ( TableProperty prop in pkprop.Properties )
				{
					GenerateParameter(cb, prop);
					cb.Append(".Value = ").Append(objName).Append(".Key.").Append(prop.PropertyName).NewLine(true);
				}
			}

			// generate other keys
			foreach ( TableProperty p in _table.Properties )
			{
				if ( p.Type == TablePropertyType.Intrinsic && ( (IntrinsicTableProperty)p ).BaseClassPK )
					continue;

				if ( p.Type != TablePropertyType.PrimaryKey )
				{
					if ( p.Nullable )
					{
						cb.Append("if ( ").Append(objName).Append(".").Append(p.PropertyName).Append(" == ").Append(p.DefaultValue).Append(" )").NewLine();
						cb.SingleLineIndent();
						GenerateParameter(cb, p);
						cb.NewLine(true);
						cb.Append("else").NewLine();
						cb.SingleLineIndent();
						GenerateParameter(cb, p);
						cb.Append(".Value = ").Append(objName).Append(".").Append(p.PropertyName).NewLine(true);

					}
					else
					{
						GenerateParameter(cb, p);
						cb.Append(".Value = ").Append(objName).Append(".").Append(p.PropertyName).NewLine(true);
					}
				}
			}
			cb.NewLine();
			cb.Append("cmd.ExecuteNonQuery()").NewLine(true);
			cb.NewLine();
			// retrieve autogenerated pk values
			foreach ( TableProperty p in pk.Properties )
			{
				if ( p.AutoGeneratedOnInsert )
				{
					cb.Append("if ( cmd.Parameters[\"@").Append(p.ColumnName).Append("\"].Value != DBNull.Value )").NewLine();
					cb.NewLine();
					cb.SingleLineIndent().Append(objName).Append(".Key.").Append(p.PropertyName).Append(" = ");
					if ( p.CSharpDataType != "string" )
					{
						cb.Append("(").Append(p.CSharpDataType).Append(")cmd.Parameters[\"@").Append(p.ColumnName).Append("\"].Value").NewLine(true);
					}
					else
					{
						cb.Append("cmd.Parameters[\"@").Append(p.ColumnName).Append("\"].Value.ToString()").NewLine(true);
					}
				}
			}
			cb.Append(objName).Append("._UpdateState(ObjectState.None)").NewLine(true);
			cb.EndBlock();

			cb.Append("finally").NewLine();

			cb.BeginBlock();
			cb.Append("if ( destroyConn && conn.State == ConnectionState.Open )").NewLine();
			
			cb.BeginBlock();
			cb.Append("conn.Close()").NewLine(true);
			cb.EndBlock();

			cb.NewLine();
			cb.Append("if ( destroyConn )").NewLine();
			
			cb.BeginBlock();
			cb.Append("conn.Dispose()").NewLine(true);
			cb.EndBlock();

			cb.EndBlock();

			cb.NewLine();
			cb.Append("return ").Append(objName).Append(".Key").NewLine(true);
			cb.EndMethod();
		}

		private void GenerateUpdate(CodeBuilder cb, Method method)
		{
			string objName = NamingHelper.ClassVariableName(_table.BusinessObjectName);

			Dictionary<string, string> parms = new Dictionary<string, string>();
			parms.Add(_table.BusinessObjectName, objName);
			parms.Add("SqlConnection", "conn");
			parms.Add("SqlTransaction", "trans");

			cb.BeginMethod(AccessibilityLevel.Public, "int", "Update", parms, OverloadType.Override);

			// check for objects with only PK params
						// check for items with only PK params
			if ( _table.Properties.Count == 1 && _table.Properties[0].Type == TablePropertyType.PrimaryKey )
			{
				// should throw an exception when updating this
				cb.Append("throw new NotSupportedException(\"The ").Append(_table.BusinessObjectName).Append(" object can not be updated; only inserted or deleted\")").NewLine(true);
			}
			else
			{


				cb.Append(objName).Append("._ConnectionStringName = _connectionStringName").NewLine(true);
				cb.NewLine();
				cb.Append("bool destroyConn = false").NewLine(true);
				cb.NewLine();
				cb.Append("if ( conn == null )").NewLine();

				cb.BeginBlock();
				cb.Append("destroyConn = true").NewLine(true);
				cb.Append("conn = new SqlConnection(_connectionString)").NewLine(true);
				cb.EndBlock();

				cb.NewLine();
				cb.Append("try").NewLine();
				cb.BeginBlock();
				cb.Append("SqlCommand cmd = new SqlCommand(\"").Append(method.StoredProcedureName).Append("\", conn)").NewLine(true);
				cb.Append("cmd.CommandType = CommandType.StoredProcedure").NewLine(true);
				cb.Append("cmd.Transaction = trans").NewLine(true);
				cb.NewLine();
				cb.Append("if ( conn.State != ConnectionState.Open )").NewLine();

				cb.BeginBlock();
				cb.Append("conn.Open()").NewLine(true);
				cb.EndBlock();

				cb.NewLine();
				if ( _table.BaseTable != null )
				{
					string dbObjectName = NamingHelper.ClassVariableName(_table.BaseTable.DbObjectName);

					cb.Append("// First, execute base class update").NewLine();
					cb.Append(_table.BaseTable.DbObjectName).Append(" ").Append(dbObjectName).Append(" = new ").Append(_table.BaseTable.DbObjectName).Append("(_connectionStringName)").NewLine(true);
					cb.Append(dbObjectName).Append(".Update(").Append(objName).Append(", conn, trans)").NewLine(true);
					cb.NewLine();
				}

				// generate primary key properties (if auto generated)
				PrimaryKeyTableProperty pk = _table.GetPrimaryKey();
				foreach ( TableProperty p in pk.Properties )
				{
					GenerateParameter(cb, p);
					cb.Append(".Value = ").Append(objName).Append(".Key.").Append(p.PropertyName).NewLine(true);
				}

				// generate base table primary keys
				if ( _table.BaseTable != null )
				{
					PrimaryKeyTableProperty pkprop = _table.BaseTable.GetPrimaryKey();
					foreach ( TableProperty prop in pkprop.Properties )
					{
						GenerateParameter(cb, prop);
						cb.Append(".Value = ").Append(objName).Append(".Key.").Append(prop.PropertyName).NewLine(true);
					}
				}

				// generate other parameters
				foreach ( TableProperty p in _table.Properties )
				{
					if ( p.Type == TablePropertyType.Intrinsic && ( (IntrinsicTableProperty)p ).BaseClassPK )
						continue;

					if ( p.Type != TablePropertyType.PrimaryKey )
					{
						if ( p.Nullable )
						{
							cb.Append("if ( ").Append(objName).Append(".").Append(p.PropertyName).Append(" == ").Append(p.DefaultValue).Append(" )").NewLine();
							cb.SingleLineIndent();
							GenerateParameter(cb, p);
							cb.NewLine(true);
							cb.Append("else").NewLine();
							cb.SingleLineIndent();
							GenerateParameter(cb, p);
							cb.Append(".Value = ").Append(objName).Append(".").Append(p.PropertyName).NewLine(true);

						}
						else
						{
							GenerateParameter(cb, p);
							cb.Append(".Value = ").Append(objName).Append(".").Append(p.PropertyName).NewLine(true);
						}
					}
				}
				cb.NewLine();
				cb.Append("int rows = cmd.ExecuteNonQuery()").NewLine(true);
				cb.NewLine();
				cb.Append(objName).Append("._UpdateState(ObjectState.None)").NewLine(true);
				cb.NewLine();
				cb.Append("return rows").NewLine(true);
				cb.EndBlock();

				cb.Append("finally").NewLine();

				cb.BeginBlock();
				cb.Append("if ( destroyConn && conn.State == ConnectionState.Open )").NewLine();
				cb.BeginBlock();
				cb.Append("conn.Close()").NewLine(true);
				cb.EndBlock();

				cb.NewLine();
				cb.Append("if ( destroyConn )").NewLine();
				cb.BeginBlock();
				cb.Append("conn.Dispose()").NewLine(true);
				cb.EndBlock();

				cb.EndBlock();
			}

			cb.EndMethod();
		}

		private void GenerateDelete(CodeBuilder cb, Method method)
		{
			string objName = NamingHelper.ClassVariableName(_table.BusinessObjectName);

			Dictionary<string, string> parms = new Dictionary<string, string>();
			parms.Add(_table.BusinessObjectName, objName);
			parms.Add("SqlConnection", "conn");
			parms.Add("SqlTransaction", "trans");

			cb.BeginMethod(AccessibilityLevel.Public, "int", "Delete", parms, OverloadType.Override);

			cb.Append("bool destroyConn = false").NewLine(true);
			cb.NewLine();
			cb.Append("if ( conn == null )").NewLine();
			
			cb.BeginBlock();
			cb.Append("destroyConn = true").NewLine(true);
			cb.Append("conn = new SqlConnection(_connectionString)").NewLine(true);
			cb.EndBlock();

			cb.NewLine();
			cb.Append("try");
			
			cb.BeginBlock();
			cb.Append("SqlCommand cmd = new SqlCommand(\"").Append(method.StoredProcedureName).Append("\", conn)").NewLine(true);
			cb.Append("cmd.CommandType = CommandType.StoredProcedure").NewLine(true);
			cb.Append("cmd.Transaction = trans").NewLine(true);
			cb.NewLine();
			cb.Append("if ( conn.State != ConnectionState.Open )").NewLine();
			
			cb.BeginBlock();
			cb.Append("conn.Open()").NewLine(true);
			cb.EndBlock();

			cb.NewLine();

			// select by pk
			PrimaryKeyTableProperty pkprop = _table.GetPrimaryKey();
			foreach ( TableProperty prop in pkprop.Properties )
			{
				GenerateParameter(cb, prop);
				cb.Append(".Value = ").Append(objName).Append(".Key.").Append(prop.PropertyName).NewLine(true);
			}

			cb.NewLine();
			cb.Append("int rows = cmd.ExecuteNonQuery()").NewLine(true);
			cb.NewLine();
			if ( _table.BaseTable != null )
			{
				string dbObjectName = NamingHelper.ClassVariableName(_table.BaseTable.DbObjectName);

				cb.Append("// Then delete subclass").NewLine();
				cb.Append("").Append(_table.BaseTable.DbObjectName).Append(" ").Append(dbObjectName).Append(" = new ").Append(_table.BaseTable.DbObjectName).Append("(_connectionStringName)").NewLine(true);
				cb.Append("").Append(dbObjectName).Append(".Delete(").Append(objName).Append(", conn, trans)").NewLine(true);
				cb.NewLine();
			}
			cb.Append(objName).Append("._UpdateState(ObjectState.Deleted)").NewLine(true);
			cb.NewLine();
			cb.Append("return rows").NewLine(true);
			cb.EndBlock();

			cb.Append("finally").NewLine();
			
			cb.BeginBlock();
			cb.Append("if ( destroyConn && conn.State == ConnectionState.Open )").NewLine();
			
			cb.BeginBlock();
			cb.Append("conn.Close()").NewLine(true);
			cb.EndBlock();

			cb.NewLine();
			cb.Append("if ( destroyConn )").NewLine();
			
			cb.BeginBlock();
			cb.Append("conn.Dispose()").NewLine(true);
			cb.EndBlock();

			cb.EndBlock();

			cb.EndMethod();
		}

		private void GenerateParameter(CodeBuilder cb, TableProperty prop)
		{
			cb.Append("cmd.Parameters.Add(\"@").Append(prop.ColumnName).Append("\", SqlDbType.").Append(prop.DataType.ToString());
			switch ( prop.DataType )
			{
				case System.Data.SqlDbType.Binary:
				case System.Data.SqlDbType.Char:
				case System.Data.SqlDbType.NChar:
				case System.Data.SqlDbType.NVarChar:
				case System.Data.SqlDbType.VarBinary:
				case System.Data.SqlDbType.VarChar:
					cb.Append(", ").Append(prop.DataLength.ToString());
					break;
			}
			cb.Append(")");
		}
	}
}
